package cn.com.nes.site.module.dao.inventory;

import cn.com.nes.common.util.Page;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.lang.UUID;
import org.apache.commons.lang3.StringUtils;
import org.jsoup.internal.StringUtil;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.math.BigDecimal;
import java.util.*;

@Repository
public class GoodsDaoImpl  extends BaseDaoImpl{
	private TransactionStatus newg_status = null;
	
	public PageData getIndex1(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			//数据概览
			List<Map<String, Object>> sjgl = new LinkedList<Map<String, Object>>();
			// 库存成本
			Map<String, Object> kccb = new LinkedHashMap<String, Object>();
			kccb.put("name", "库存成本");
			kccb.put("value", "0");
			sjgl.add(kccb);
			// 今日入库
			sb.delete(0, sb.length());
			sb.append(" SELECT count(distinct orderId) order_in from g_order_in where 1=1 ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = getDateType(dateType,"warehouseTime");
            	sb.append(query);
			}
			int orderin= this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[]{}, Integer.class);
			Map<String, Object> order_in = new LinkedHashMap<String, Object>();
			order_in.put("name", "入库");
			order_in.put("value", orderin);
			sjgl.add(order_in);
			// 今日出库
			sb.delete(0, sb.length());
			sb.append(" SELECT count(distinct orderId) order_out from g_order_out where 1=1 ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = getDateType(dateType,"warehouseTime");
            	sb.append(query);
			}
			int orderout= this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[]{}, Integer.class);
			Map<String, Object> order_out = new LinkedHashMap<String, Object>();
			order_out.put("name", "出库");
			order_out.put("value", orderout);
			sjgl.add(order_out);
			// 库存过低 \库存过高 
			sb.delete(0, sb.length());
			sb.append(" SELECT sum(if(a.endingNum<=b.numberLow,1,0)) account_low,sum(if(a.endingNum>=b.numberHight,1,0)) account_hot" +
					" FROM g_account a LEFT JOIN g_goods b on a.goodsID=b.uuid " +
					" WHERE a.isNew = '1' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = getDateType(dateType,"a.createTime");
            	sb.append(query);
			}
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{});
			Map<String, Object> account_low = new LinkedHashMap<String, Object>();
			account_low.put("name", "库存过低");
			account_low.put("value", list.get(0).get("account_low"));
			sjgl.add(account_low);
			Map<String, Object> account_hot = new LinkedHashMap<String, Object>();
			account_hot.put("name", "库存过高");
			account_hot.put("value", list.get(0).get("account_hot"));
			sjgl.add(account_hot);
			// 库存周转率
			sb.delete(0, sb.length());
			StringBuffer sbadd1 = new StringBuffer();
			StringBuffer sbadd2 = new StringBuffer();
			sbadd1.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sbadd2.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query1 = getDateType(dateType,"warehouseTime");
            	sbadd1.append(query1);
            	String query2 = getDateType(dateType,"createTime");
            	sbadd2.append(query2);
			}
			sb.append(" SELECT ifnull(round(a.inoutNum/b.endingNum*100,2),0) account_zzl FROM " +
					" (SELECT sum(inoutNum) inoutNum FROM g_order_in where 1=1 ").append(sbadd1).append(" ) a, " +
					" (SELECT sum(endingNum) endingNum FROM g_account where 1=1 ").append(sbadd2).append(" ) b ");
			int accountzzl= this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[]{}, Integer.class);
			Map<String, Object> account_zzl = new LinkedHashMap<String, Object>();
			account_zzl.put("name", "库存周转率");
			account_zzl.put("value", accountzzl+"%");
			sjgl.add(account_zzl);
			
			res.setExtData(sjgl);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getIndex2(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try { 
			// 设备出库
			sb.delete(0, sb.length());
			sb.append(" SELECT b.goodsName," +
					" sum(if(a.warehouseTime between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day),inoutNum,0)) thismonth, " +
					" sum(if(a.warehouseTime between SUBDATE(curdate(),INTERVAL 2 month) and SUBDATE(curdate(),INTERVAL 1 month),inoutNum,0)) lastmoth " +
					" from g_order_out a " +
					" left join g_goods b on a.goodsUUid = b.uuid where b.tabPosition='02' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" and a.warehouseTime between SUBDATE(curdate(),INTERVAL 2 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			sb.append(" GROUP BY a.goodsUUid ");
			List<Map<String, Object>> list1 = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{});
			String[] list1_x=new String[list1.size()];
			BigDecimal[] list1_y1=new BigDecimal[list1.size()];
			BigDecimal[] list1_y2=new BigDecimal[list1.size()];
			for(int i=0;i<list1.size();i++) {
				list1_x[i]=(String) list1.get(i).get("goodsName");
				list1_y1[i]=(BigDecimal) list1.get(i).get("thismonth");
				list1_y2[i]=(BigDecimal) list1.get(i).get("lastmoth");
			}
			extData.put("list1_x", list1_x);
			extData.put("list1_y1", list1_y1);
			extData.put("list1_y2", list1_y2);
			// 配件出库
			sb.delete(0, sb.length());
			sb.append(" SELECT b.goodsName," +
					" sum(if(a.warehouseTime between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day),inoutNum,0)) thismonth, " +
					" sum(if(a.warehouseTime between SUBDATE(curdate(),INTERVAL 2 month) and SUBDATE(curdate(),INTERVAL 1 month),inoutNum,0)) lastmoth " +
					" from g_order_out a " +
					" left join g_goods b on a.goodsUUid = b.uuid where b.tabPosition='01' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" and a.warehouseTime between SUBDATE(curdate(),INTERVAL 2 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			sb.append(" GROUP BY a.goodsUUid ");
			List<Map<String, Object>> list2 = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{});
			BigDecimal[] list2_x1=new BigDecimal[list2.size()];
			BigDecimal[] list2_x2=new BigDecimal[list2.size()];
			String[] list2_y=new String[list2.size()];
			for(int i=0;i<list2.size();i++) {
				list2_x1[i]=(BigDecimal) list2.get(i).get("thismonth");
				list2_x2[i]=(BigDecimal) list2.get(i).get("lastmoth");
				list2_y[i]=(String) list2.get(i).get("goodsName");
			}
			extData.put("list2_x1", list2_x1);
			extData.put("list2_x2", list2_x2);
			extData.put("list2_y", list2_y);
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getIndex3(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			// 入库TOP10
			sb.delete(0, sb.length());
			sb.append(" SELECT c.customerID,b.customerName,c.phone,c.cusname,c.goodsUUid,b.goodsCode,b.goodsName,sum(a.inoutNum) inoutNum " +
					" FROM g_order_in a " +
					" LEFT JOIN g_goods b ON a.goodsUUid = b.uuid " +
					" LEFT JOIN g_order_in_apply c on a.applyId=c.applyId and c.serial='1' " +
					" WHERE a.isdelete <> '1' " );
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = getDateType(dateType,"a.warehouseTime");
            	sb.append(query);
			}
			sb.append(" GROUP BY a.goodsUUid,c.customerID ORDER BY sum(a.inoutNum) desc LIMIT 10 ");
			List<Map<String, Object>> list_in = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{});
			res.setExtData(list_in);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getIndex4(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			// 出库TOP10
			sb.delete(0, sb.length());
			sb.append(" SELECT c.customerID,b.customerName,c.phone,c.cusname,c.goodsUUid,b.goodsCode,b.goodsName,sum(a.inoutNum) inoutNum " +
					" FROM g_order_out a " +
					" LEFT JOIN g_goods b ON a.goodsUUid = b.uuid " +
					" LEFT JOIN g_order_out_apply c on a.applyId=c.applyId and c.serial='1' " +
					" WHERE a.isdelete <> '1' " );
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if(!StringUtils.isEmpty((String)param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = getDateType(dateType,"a.warehouseTime");
            	sb.append(query);
			}
			sb.append(" GROUP BY a.goodsUUid,c.customerID ORDER BY sum(a.inoutNum) desc LIMIT 10 ");
			List<Map<String, Object>> list_out = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{});
			res.setExtData(list_out);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getAccount(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select a.*,b.goodsType,c.goodsTypeName,b.goodsModel,b.customerId,b.customerName,b.brand ");
			sb.append(" from g_account a ");
			sb.append(" LEFT JOIN g_goods b ON a.goodsID=b.uuid and b.isdelete <> '1' ");
			sb.append(" LEFT JOIN g_goodstype c ON b.goodsType=c.goodsTypeId and b.companyId = c.companyId and c.isdelete = 0");
			sb.append(" where a.isNew = '1' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			if (!StringUtils.isEmpty((String)param.get("warehouseId"))){
				sb.append(" and a.warehouseId like '").append(param.get("warehouseId")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("goodsType"))){
				sb.append(" and b.goodsType like '").append(param.get("goodsType")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("brand"))){
				sb.append(" and b.brand like '%").append(param.get("brand")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("key"))){
				sb.append(" and (b.goodsName like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or b.goodsCode like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or b.goodsModel like '%").append(param.get("key")).append("%') ") ;
			}
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" group by a.goodsID,b.goodsType,a.warehouseId ");
			sb.append(" order by a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
			System.out.println(sb);
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getGoods(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select c.companyName customerName,a.*,b.goodsTypeName from g_goods a ");
			sb.append(" left join g_goodstype b on a.goodsType=b.goodsTypeId and a.companyId = b.companyId and b.isdelete = 0");
			sb.append(" left join t_company c on a.customerId=c.companyId ");
			sb.append(" where a.isdelete <> '1' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" and a.tabPosition = '").append(param.get("tabPosition")).append("' ") ;//货品清单类型
			if (!StringUtils.isEmpty((String)param.get("goodsType"))){
				sb.append(" and a.goodsType like '").append(param.get("goodsType")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("brand"))){
				sb.append(" and a.brand like  '%").append(param.get("brand")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("key"))){
				sb.append(" and (a.goodsName like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or a.goodsCode like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or a.sblx like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or a.sbxh like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or a.sbmc like '%").append(param.get("key")).append("%' ") ;
				sb.append(" or a.goodsModel like '%").append(param.get("key")).append("%') ") ;
			}
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" order by a.createTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;			
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
	public PageData saveGoods(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "g_goods");
		PageData sqlInfo = null;
		try {
			//判断设备型号是否存在
			StringBuffer sb = new StringBuffer();
			sb.append("select count(1) cnt from g_goods where isdelete=0 and companyId='")
			.append(req.getUinfo().getCompanyId()).append("' ")
			.append(" and sbxh='").append(param.get("sbxh")).append("' ");
			if(!StringUtils.isEmpty((String) param.get("uuid"))) {
				sb.append(" and uuid!='").append(param.get("uuid")).append("' ");
			}
			int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
			if(cnt>0) {
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("设备型号已存在!");
				return res;
			}
			
			String uuid=(String)param.get("uuid");
			param.put("companyId", req.getUinfo().getCompanyId());
			if (uuid ==null || "".equals(uuid)){
				param.put("isdelete", "0");
				uuid = UUID.randomUUID().toString();
				param.put("uuid",uuid) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "uuid") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
		
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData delGoods(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除信息失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" update g_goods set isdelete='1' ");
			sb.append(" WHERE uuid = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("uuid")});
			
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public List<?> getChild(String id, List<Map<String, Object>> data) {
		List<Object> lists = new ArrayList<Object>();
		for (Map<String, Object> map : data) {
			String cg_pig = map.get("pId").toString();
			if (cg_pig.equals(id)) {
				// 组装子节点，开始循环去获取子节点
				List<?> aaa=getChild(map.get("value").toString(), data);
				if(aaa.size()>0){
					map.put("children",aaa);
				}
				lists.add(map);
			}
		}
		return lists;
	}
	
	public PageData getGoodsType(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select * from g_goodstype where isdelete = 0 and P_goodsTypeId='0000' ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" order by goodsTypeId desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;	
//			System.out.println(sb);
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			List<Map<String, Object>> failList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> tmp : list) {
				sb = new StringBuffer();
				sb.append(" select * from g_goodstype where isdelete = 0 and goodsTypeId like '").append(tmp.get("goodsTypeId")).append("%'");
				sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
				List<Map<String, Object>> listChild = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
				List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
				List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();
				for (Map<String, Object> tmpChild : listChild) {
					Map<String, Object> map = new LinkedHashMap<String, Object>();
					if("0000".equals(tmpChild.get("P_goodsTypeId"))){
						map.put("pId", "");
						map.put("pId_nm", "");
					}else{
						map.put("pId", tmpChild.get("P_goodsTypeId"));
						map.put("pId_nm", tmpChild.get("P_goodsTypeName"));
					}
					map.put("value", tmpChild.get("goodsTypeId"));
					map.put("label", tmpChild.get("goodsTypeName"));
					map.put("memo", tmpChild.get("memo"));
					map.put("type", tmpChild.get("goodsTypeType"));
					allList.add(map);
				}
				// 获取父节点
				for (Map<String, Object> tmpChild : allList) {
					String cg_pig = tmpChild.get("pId").toString();
					if (StringUtil.isBlank(cg_pig)) {
						// 组装子节点，开始循环去获取子节点
						List<?> aaa=getChild(tmpChild.get("value").toString(), allList);
						if(aaa.size()>0){
							tmpChild.put("children",aaa);
						}
						pList.add(tmpChild);
					}
				}
				failList.addAll(pList);
			}
			
			res.setPage(failList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
	public PageData saveGoodsType(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "g_goodstype");
		PageData sqlInfo = null;
		try {
			String goodsTypeId=(String)param.get("goodsTypeId");
			param.put("companyId", req.getUinfo().getCompanyId());
			
			//验证货品类别唯一性-start
			int cnt=getGoodsNameCnt(param.get("P_goodsTypeId").toString(),param.get("goodsTypeName").toString(),req.getUinfo().getCompanyId());
			if(cnt>0){
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("该货品类别已存在！！！");
				return res;
			}
			//验证货品类别唯一性-end
			
			if (goodsTypeId ==null || "".equals(goodsTypeId)){
				param.put("isdelete", "0");
				goodsTypeId = getNextGoodsTypeId(param.get("P_goodsTypeId").toString(), req.getUinfo().getCompanyId());
				param.put("goodsTypeId",goodsTypeId) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "goodsTypeId") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
		
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public int getGoodsNameCnt(String p_Id,String name,String companyId) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT count(0) cnt from g_goodstype where P_goodsTypeId=? and goodsTypeName=? and companyId=? and isdelete <> '1'");
		int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[]{p_Id,name,companyId}, Integer.class);
		return cnt;
	}
	
	public String getNextGoodsTypeId(String p_Id, String companyId) {
		String retId="";
		String id="";
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT goodsTypeId from g_goodstype where isdelete <> '1' and P_goodsTypeId=? and companyId = ? order by goodsTypeId desc limit 1 ");
		List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{p_Id, companyId});
		String goodsTypeId = list.size()>0 ? list.get(0).get("goodsTypeId").toString() : "0000";
		id =  goodsTypeId.length() > 4 ? goodsTypeId.substring(goodsTypeId.length()-4) : goodsTypeId ;
		int nextId=Integer.parseInt(id)+ 1;
		id = String.format("%04d", nextId);
		if("0000".equals(p_Id)){
			retId=id;
		}else{
			retId=p_Id+id;
		}

		return retId;
	}

	public PageData delGoodsType(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除信息失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" update g_goodstype set isdelete = 1 ");
			sb.append(" WHERE goodsTypeId = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("goodsTypeId")});
		    
		    sb = new StringBuffer();
			sb.append(" update g_goodstype set isdelete = 1 ");
			sb.append(" WHERE P_goodsTypeId = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("goodsTypeId")});
			
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getWarehouse(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select * from g_warehouse where isdelete <> '1' and P_warehouseId='0000' ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" order by warehouseId desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;			
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			List<Map<String, Object>> failList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> tmp : list) {
				sb = new StringBuffer();
				sb.append(" select * from g_warehouse where isdelete <> '1' and warehouseId like '").append(tmp.get("warehouseId")).append("%'");
				sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
				List<Map<String, Object>> listChild = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
				List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
				List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();
				for (Map<String, Object> tmpChild : listChild) {
					Map<String, Object> map = new LinkedHashMap<String, Object>();
					if("0000".equals(tmpChild.get("P_warehouseId"))){
						map.put("pId", "");
						map.put("pId_nm", "");
					}else{
						map.put("pId", tmpChild.get("P_warehouseId"));
						map.put("pId_nm", tmpChild.get("P_warehouseName"));
					}
					map.put("value", tmpChild.get("warehouseId"));
					map.put("label", tmpChild.get("warehouseName"));
					map.put("memo", tmpChild.get("memo"));
					allList.add(map);
				}
				// 获取父节点
				for (Map<String, Object> tmpChild : allList) {
					String cg_pig = tmpChild.get("pId").toString();
					if (StringUtil.isBlank(cg_pig)) {
						// 组装子节点，开始循环去获取子节点
						List<?> aaa=getChild(tmpChild.get("value").toString(), allList);
						if(aaa.size()>0){
							tmpChild.put("children",aaa);
						}
						pList.add(tmpChild);
					}
				}
				failList.addAll(pList);
			}
			
			res.setPage(failList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
	public PageData saveWarehouse(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "g_warehouse");
		PageData sqlInfo = null;
		try {
			String warehouseId=(String)param.get("warehouseId");
			param.put("companyId", req.getUinfo().getCompanyId());
			if (warehouseId ==null || "".equals(warehouseId)){
				param.put("isdelete", "0");
				warehouseId = getNextWarehouseTypeId(param.get("P_warehouseId").toString());
				param.put("warehouseId",warehouseId) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "warehouseId") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
		
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public String getNextWarehouseTypeId(String p_Id) {
		String retId="";
		String id="";
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT warehouseId from g_warehouse where P_warehouseId=? order by warehouseId desc limit 1 ");
		List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{p_Id});
		if(list.size()>0){
			id=list.get(0).get("warehouseId").toString().substring(list.get(0).get("warehouseId").toString().length()-2,list.get(0).get("warehouseId").toString().length());
		}else{
			id="0000";
		}
		int nextId=Integer.parseInt(id)+1;
		if(nextId>999){
			id=String.valueOf(nextId);
		}else if(nextId>99&&nextId<=999){
			id=String.valueOf("0"+nextId);
		}else if(nextId>9&&nextId<=99){
			id=String.valueOf("00"+nextId);
		}else if(nextId<=9){
			id=String.valueOf("000"+nextId);
		}
		if("0000".equals(p_Id)){
			retId=id;
		}else{
			retId=p_Id+id;
		}
		return retId;
	}
	
	public PageData delWarehouse(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除信息失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" update g_warehouse set isdelete='1' ");
			sb.append(" WHERE warehouseId = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("warehouseId")});
		    
		    sb = new StringBuffer();
			sb.append(" update g_warehouse set isdelete='1' ");
			sb.append(" WHERE P_warehouseId = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("warehouseId")});
			
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			rollback_newg(newg_status);
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getGoodsTypeSelect(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select * from g_goodstype where isdelete <> '1' ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" order by goodsTypeId desc ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();// eTree
			for (Map<String, Object> tmp : list) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				if("0000".equals(tmp.get("P_goodsTypeId"))){
					map.put("pId", "");
					map.put("pId_nm", "");
				}else{
					map.put("pId", tmp.get("P_goodsTypeId"));
					map.put("pId_nm", tmp.get("P_goodsTypeName"));
				}
				map.put("value", tmp.get("goodsTypeId"));
				map.put("label", tmp.get("goodsTypeName"));
				map.put("type", tmp.get("goodsTypeType"));
				allList.add(map);
			}
			// 获取父节点
			for (Map<String, Object> tmpChild : allList) {
				String cg_pig = tmpChild.get("pId").toString();
				if (StringUtil.isBlank(cg_pig)) {
					// 组装子节点，开始循环去获取子节点
					List<?> aaa=getChild(tmpChild.get("value").toString(), allList);
					if(aaa.size()>0){
						tmpChild.put("children",aaa);
					}
					pList.add(tmpChild);
				}
			}
		
			res.setPage(pList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}
	
	public PageData getWarehouseSelect(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select * from g_warehouse where isdelete <> '1' ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" order by warehouseId desc ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();// eTree
			for (Map<String, Object> tmp : list) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				if("0000".equals(tmp.get("P_warehouseId"))){
					map.put("pId", "");
					map.put("pId_nm", "");
				}else{
					map.put("pId", tmp.get("P_warehouseId"));
					map.put("pId_nm", tmp.get("P_warehouseName"));
				}
				map.put("value", tmp.get("warehouseId"));
				map.put("label", tmp.get("warehouseName"));
				allList.add(map);
			}
			// 获取父节点
			for (Map<String, Object> tmpChild : allList) {
				String cg_pig = tmpChild.get("pId").toString();
				if (StringUtil.isBlank(cg_pig)) {
					// 组装子节点，开始循环去获取子节点
					List<?> aaa=getChild(tmpChild.get("value").toString(), allList);
					if(aaa.size()>0){
						tmpChild.put("children",aaa);
					}
					pList.add(tmpChild);
				}
			}
			
			res.setPage(pList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getBrandSelect(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" select brand value,brand label from g_goods where isdelete <> '1' ");
			sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" group by brand ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getCompanySelect(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			String companyType = "01";
//			String type = req.getUinfo().getCompanyType();
//			if(!"00".equals(type)) {
//				companyType = type;
//			}
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT a.companyId,a.companyName,a.brand,a.address,a.legalPerson cusname,a.contactNumber phone ");
			sb.append(" FROM t_company a ");
			sb.append(" LEFT JOIN t_agent_facturer b ON b.facturer_id = a.companyId and b.deleted<>'1' ");
			sb.append(" WHERE 1=0 or (a.companyType = '" + companyType + "' and a.sfsc<>'1' and b.agent_id =? ) ");
			if( !"01".equals(param.get("ishfg"))){
				 sb.append("  or companyId='").append(req.getUinfo().getCompanyId()).append("' ");
			 }
			sb.append(" GROUP BY a.companyId ");
			//System.out.println(sb);
			//System.out.println(req.getUinfo().getCompanyId());
			List<Map<String, Object>> list =this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getGoodsSelect(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT a.goodsTypeId,a.goodsTypeName,a.P_goodsTypeId,a.P_goodsTypeName,null packingSize,null goodsModel,null brand,'0' isgoods FROM g_goodstype a ");
			sb.append(" WHERE a.isdelete =0 ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			sb.append(" AND (SELECT count(0) from g_goods where goodsType like CONCAT(a.goodsTypeId,'%')) <>0 ");
			sb.append(" UNION ");
			sb.append(" SELECT a.uuid goodsTypeId,a.goodsName goodsTypeName,a.goodsType P_goodsTypeId,b.goodsTypeName P_goodsTypeName,a.packingSize,a.goodsModel,a.brand,'1' isgoods FROM g_goods a ");
			sb.append(" LEFT JOIN g_goodstype b ON a.goodsType=b.goodsTypeId and b.isdelete = 0 and a.companyId = b.companyId ");
			sb.append(" WHERE a.isdelete <> '1' ");
			sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			System.out.println(sb);
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();// eTree
			for (Map<String, Object> tmp : list) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				if(tmp.get("P_goodsTypeId") == null || "0000".equals(tmp.get("P_goodsTypeId"))){
					map.put("pId", "");
					map.put("pId_nm", "");
				}else{
					map.put("pId", tmp.get("P_goodsTypeId"));
					map.put("pId_nm", tmp.get("P_goodsTypeName"));
				}
				map.put("value", tmp.get("goodsTypeId"));
				map.put("label", tmp.get("goodsTypeName"));
				map.put("isgoods", tmp.get("isgoods"));
				
				map.put("packingSize", tmp.get("packingSize"));
				map.put("goodsModel", tmp.get("goodsModel"));
				map.put("brand", tmp.get("brand"));
				allList.add(map);
			}
			// 获取父节点
			for (Map<String, Object> tmpChild : allList) {
				String cg_pig = tmpChild.get("pId").toString();
				if (StringUtil.isBlank(cg_pig)) {
					// 组装子节点，开始循环去获取子节点
					List<?> aaa=getChild(tmpChild.get("value").toString(), allList);
					if(aaa.size()>0){
						tmpChild.put("children",aaa);
					}
					pList.add(tmpChild);
				}
			}
		
			res.setPage(pList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
}
